#include <tasks_manager/tasks_db.h>

// 构造函数
TaskDB::TaskDB(string task_db_path, string task_record_db_path){
    __task_db_sqlite = boost::make_shared<SqliteBridge>(task_db_path);
    __task_record_db_sqlite = boost::make_shared<SqliteBridge>(task_record_db_path);
}

// 析构函数
TaskDB::~TaskDB(){

}

// 通用数据库指令执行 __task_db
Json::Value TaskDB::TaskDBSqlite3Exec(string& sql_cmd){
    std::lock_guard<std::mutex> guard(__task_db_mutex);
    return __task_db_sqlite->Sqlite3Exec(sql_cmd);
}

// 通用数据库指令执行 __task_record_db
Json::Value TaskDB::TaskRecordDBSqlite3Exec(string& sql_cmd){
    std::lock_guard<std::mutex> guard(__task_db_mutex);
    return __task_record_db_sqlite->Sqlite3Exec(sql_cmd);
}


//创建工作表
void TaskDB::CreateTables(){

    // 校验字段是否存在
    auto CheckFieldExists = [](string field_name, vector<pair<string,string>> table_info) -> bool
    {
        for(auto& info : table_info){
            if(info.first == field_name) return true;
        }
        return false;
    };

    string sql_cmd;
    Json::Value json_data;
    // 创建单个任务表
    sql_cmd = "CREATE TABLE IF NOT EXISTS TASK_SINGLE ( \
        uuid                TEXT        PRIMARY KEY     NOT NULL, \
        task_name           TEXT        NOT NULL, \
        task_id             TEXT        NOT NULL, \
        map_name            TEXT        NOT NULL, \
        map_base            TEXT        NOT NULL, \
        next_map_base       TEXT        NOT NULL, \
        group_name          TEXT        NOT NULL, \
        group_id            TEXT        NOT NULL, \
        task_type           TEXT        NOT NULL, \
        start               TEXT        NOT NULL, \
        polygon             TEXT        NOT NULL, \
        device_type         TEXT        NOT NULL, \
        control_mode        TEXT        NOT NULL, \
        edge_mode           TEXT        NOT NULL, \
        task_speed          TEXT        NOT NULL, \
        paths               TEXT        NOT NULL, \
        edge_paths          TEXT        NOT NULL, \
        pose                TEXT        NOT NULL, \
        dynamic_adjust_path TEXT        NOT NULL, \
        utils_mode          TEXT        NOT NULL, \
        estimated_time      TEXT        NOT NULL, \
        task_area           TEXT        NOT NULL, \
        task_echo           TEXT        NOT NULL, \
        path_gen            TEXT        NOT NULL, \
        is_top              TEXT        NOT NULL, \
        save_time           DATETIME    NOT NULL \
    ) ";
    TaskDBSqlite3Exec(sql_cmd);

    // 创建组合任务表
    sql_cmd = "CREATE TABLE IF NOT EXISTS TASK_GROUP ( \
        group_name              TEXT        PRIMARY KEY     NOT NULL, \
        group_id                TEXT        NOT NULL, \
        timertask_name          TEXT        NOT NULL, \
        timertask_id            TEXT        NOT NULL, \
        task_list               TEXT        NOT NULL, \
        is_top                  TEXT        NOT NULL, \
        save_time               DATETIME    NOT NULL \
    ) ";
    TaskDBSqlite3Exec(sql_cmd);

    // 创建定时任务表
    sql_cmd = "CREATE TABLE IF NOT EXISTS TIMER_TASK ( \
        timertask_name          TEXT        PRIMARY KEY     NOT NULL, \
        timertask_id            TEXT        NOT NULL, \
        enable                  TEXT        NOT NULL, \
        start_time              TEXT        NOT NULL, \
        forced_end_time         TEXT        NOT NULL, \
        forced_end_time_enable  TEXT        NOT NULL, \
        timetable               TEXT        NOT NULL, \
        taskgroup_list          TEXT        NOT NULL, \
        estimated_time          TEXT        NOT NULL, \
        is_top                  TEXT        NOT NULL, \
        multi_maps_shared       TEXT        NOT NULL, \
        map_name                TEXT        NOT NULL, \
        save_time               DATETIME    NOT NULL \
    ) ";
    TaskDBSqlite3Exec(sql_cmd);

    // 更新字段
    vector<pair<string,string>> table_info = __task_db_sqlite->TableInfo("TIMER_TASK");
    if(!CheckFieldExists("multi_maps_shared", table_info)){
        sql_cmd = "ALTER TABLE TIMER_TASK ADD COLUMN multi_maps_shared TEXT DEFAULT 'false'";
        TaskDBSqlite3Exec(sql_cmd);
    }
    if(!CheckFieldExists("map_name", table_info)){
        sql_cmd = "ALTER TABLE TIMER_TASK ADD COLUMN map_name TEXT DEFAULT ''";
        TaskDBSqlite3Exec(sql_cmd);
    }
    
    // 创建单个任务记录数据
    sql_cmd = "CREATE TABLE IF NOT EXISTS TASK_SINGLE_RECORD ( \
        task_id             TEXT        PRIMARY KEY     NOT NULL, \
        task_name           TEXT        NOT NULL, \
        map_name            TEXT        NOT NULL, \
        group_name          TEXT        NOT NULL, \
        group_id            TEXT        NOT NULL, \
        timertask_name      TEXT        NOT NULL, \
        timertask_id        TEXT        NOT NULL, \
        add_time            TEXT        NOT NULL, \
        start_time          TEXT        NOT NULL, \
        end_time            TEXT        NOT NULL, \
        task_status         TEXT        NOT NULL, \
        progress            TEXT        NOT NULL, \
        task_single         TEXT        NOT NULL, \
        is_assigned         TEXT        NOT NULL, \
        save_time           DATETIME    NOT NULL \
    ) ";
    TaskRecordDBSqlite3Exec(sql_cmd);
    // 更新字段
    vector<pair<string,string>> task_single_record_table_info = __task_db_sqlite->TableInfo("TASK_SINGLE_RECORD");
    if(!CheckFieldExists("is_assigned", task_single_record_table_info)){
        sql_cmd = "ALTER TABLE TASK_SINGLE_RECORD ADD COLUMN is_assigned TEXT DEFAULT ''";
        TaskRecordDBSqlite3Exec(sql_cmd);
    }

    // 创建任务报告表
    sql_cmd = "CREATE TABLE IF NOT EXISTS TASK_REPORT ( \
        group_id                TEXT        PRIMARY KEY     NOT NULL, \
        group_name              TEXT        NOT NULL, \
        timertask_name          TEXT        NOT NULL, \
        timertask_id            TEXT        NOT NULL, \
        start_time              TEXT        NOT NULL, \
        end_time                TEXT        NOT NULL, \
        working_time            TEXT        NOT NULL, \
        total_area              TEXT        NOT NULL, \
        cleaned_area            TEXT        NOT NULL, \
        uncleaned_area          TEXT        NOT NULL, \
        cleaning_efficiency     TEXT        NOT NULL, \
        coverage_rate           TEXT        NOT NULL, \
        error_code              TEXT        NOT NULL, \
        task_pictures           TEXT        NOT NULL, \
        status                  TEXT        NOT NULL, \
        task_group              TEXT        NOT NULL, \
        is_assigned             TEXT        NOT NULL, \
        save_time               DATETIME    NOT NULL \
    ) ";
    TaskRecordDBSqlite3Exec(sql_cmd);
    // 更新字段
    vector<pair<string,string>> task_report_table_info = __task_db_sqlite->TableInfo("TASK_REPORT");
    if(!CheckFieldExists("is_assigned", task_report_table_info)){
        sql_cmd = "ALTER TABLE TASK_REPORT ADD COLUMN is_assigned TEXT DEFAULT ''";
        TaskRecordDBSqlite3Exec(sql_cmd);
    }
    if(!CheckFieldExists("status", task_report_table_info)){
        sql_cmd = "ALTER TABLE TASK_REPORT ADD COLUMN status TEXT DEFAULT 'UNKNOWN'";
        TaskRecordDBSqlite3Exec(sql_cmd);
    }
}


// 清除为空的数据
void TaskDB::DeleteNullData(){
    string sql_cmd = "DELETE FROM TASK_SINGLE WHERE task_name = ''";
    TaskDBSqlite3Exec(sql_cmd); 
    sql_cmd = "DELETE FROM TASK_GROUP WHERE group_name = ''";
    TaskDBSqlite3Exec(sql_cmd); 
    sql_cmd = "DELETE FROM TIMER_TASK WHERE timertask_name = ''";
    TaskDBSqlite3Exec(sql_cmd); 
    sql_cmd = "DELETE FROM TASK_SINGLE_RECORD WHERE task_id = ''";
    TaskRecordDBSqlite3Exec(sql_cmd); 
    sql_cmd = "DELETE FROM TASK_REPORT WHERE group_id = ''";
    TaskRecordDBSqlite3Exec(sql_cmd); 

    // 清空无效磁盘数据
    sql_cmd = "vacuum";
    TaskDBSqlite3Exec(sql_cmd); 
    TaskRecordDBSqlite3Exec(sql_cmd);
}

/*******************************************
 * 单个任务基础操作
 *******************************************/  
// 插入单个任务
void TaskDB::InsertTaskSingle(Json::Value& json_data){
    string sql_cmd = "INSERT INTO TASK_SINGLE (uuid,task_name,task_id,map_name,map_base,next_map_base,group_name,\
        group_id,task_type,start,polygon,device_type,control_mode,edge_mode,task_speed,\
        paths,edge_paths,pose,dynamic_adjust_path,utils_mode,estimated_time,task_area,\
        task_echo,path_gen,is_top,save_time) VALUES ( hex(randomblob(16)),'";
    sql_cmd += json_data["task_base"]["task_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["task_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["map_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["map_base"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["next_map_base"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["group_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["group_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["task_type"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["start"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["polygon"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["device_type"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["control_mode"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["edge_mode"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["task_speed"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["paths"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["edge_paths"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["pose"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["dynamic_adjust_path"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["utils_mode"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["estimated_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["task_area"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_echo"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["path_gen"].toStyledString(); 
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["is_top"].asString();
    sql_cmd += "','";
    sql_cmd += __msg_convert.TimestampFormat(ros::Time::now().sec);
    sql_cmd += "')";
    // cout << "[InsertTaskSingle]sql_cmd: " << sql_cmd << endl;
    TaskDBSqlite3Exec(sql_cmd);
}

// 插入单个任务
void TaskDB::InsertTaskSingle(TaskSingle& task){
    Json::Value json_data = __msg_convert.TaskSingle2Json(task);
    // cout << "[InsertTaskSingle]json_data: " << json_data << endl;
    InsertTaskSingle(json_data);
}


// 更新单个任务
void TaskDB::UpdateTaskSingle(string map_name, string task_name, TaskSingle& task){
    Json::Value json_data = __msg_convert.TaskSingle2Json(task);
    string sql_cmd = "UPDATE TASK_SINGLE SET task_name='";
    sql_cmd += json_data["task_base"]["task_name"].asString();
    sql_cmd += "', task_speed='";
    sql_cmd += json_data["task_base"]["task_speed"].asString();
    sql_cmd += "', utils_mode='";
    sql_cmd += json_data["task_base"]["utils_mode"].asString();
    sql_cmd += "', control_mode='";
    sql_cmd += json_data["task_base"]["control_mode"].asString();
    sql_cmd += "', path_gen='";
    sql_cmd += json_data["task_base"]["path_gen"].toStyledString();
    sql_cmd += "', task_area='";
    sql_cmd += json_data["task_base"]["task_area"].asString();
    sql_cmd += "', paths='";
    sql_cmd += json_data["task_base"]["paths"].toStyledString();
    sql_cmd += "', edge_paths='";
    sql_cmd += json_data["task_base"]["edge_paths"].toStyledString();
    sql_cmd += "' WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "' AND task_name='";
    sql_cmd += task_name;
    sql_cmd += "'";
    // cout << "[UpdateTaskSingle] sql_cmd: " << sql_cmd << endl;
    TaskDBSqlite3Exec(sql_cmd);
}

// 获取任务UUID
string TaskDB::GetTaskSingleUuid(string map_name, string task_name){
    string uuid;
    string sql_cmd = "SELECT uuid FROM TASK_SINGLE WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "' AND task_name='";
    sql_cmd += task_name;
    sql_cmd += "'";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    if(!json_data.isNull()){
        uuid = json_data[0]["uuid"].asString();
    }
    return uuid;
}

//  检测任务是否存在
bool TaskDB::CheckTaskSingleExist(string map_name, string task_name){
    string uuid = GetTaskSingleUuid(map_name, task_name);
    return !uuid.empty();
}

// 删除任务
void TaskDB::DeleteTaskSingle(string map_name, string task_name){
    string sql_cmd = "DELETE FROM TASK_SINGLE WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "' AND task_name='";
    sql_cmd += task_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 删除指定地图下所有任务
void TaskDB::DeleteTaskSingleByMapName(string map_name){
    string sql_cmd = "DELETE FROM TASK_SINGLE WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd); 
}

// 删除所有任务
void TaskDB::DeleteTaskSingleAll(){
    string sql_cmd = "DELETE FROM TASK_SINGLE";
    TaskDBSqlite3Exec(sql_cmd);
    // 兼容操作
    sql_cmd = "DELETE FROM TASK_GROUP";
    TaskDBSqlite3Exec(sql_cmd);
    sql_cmd = "DELETE FROM TIMER_TASK";
    TaskDBSqlite3Exec(sql_cmd);
}

// 重命名任务
void TaskDB::RenameTaskSingle(string map_name, string old_task_name, string new_task_name){
    string sql_cmd = "UPDATE TASK_SINGLE SET task_name='";
    sql_cmd += new_task_name;
    sql_cmd += "' WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "' AND task_name='";
    sql_cmd += old_task_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 获取指定地图下任务列表 按保存时间倒序
vector<TaskSingle> TaskDB::GetTaskSingleList(string map_name){
    vector<TaskSingle> task_list;
    string sql_cmd = "SELECT * FROM TASK_SINGLE WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "' ORDER BY save_time DESC";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    for(int i=0; i<json_data.size(); ++i){
        task_list.emplace_back(__msg_convert.Json2TaskSingle(json_data[i]));
    }
    return task_list;
}

// 获取所有地图下的数据
unordered_map<string, vector<TaskSingle>> TaskDB::GetAllTaskSingleList(){
    unordered_map<string, vector<TaskSingle>> all_task_list;
    string sql_cmd = "SELECT * FROM TASK_SINGLE ORDER BY map_name,save_time DESC";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    for(int i=0; i<json_data.size(); ++i){
        string map_name = json_data[i]["map_name"].asString();
        if(all_task_list.find(map_name) == all_task_list.end()){
            vector<TaskSingle> task_list;
            task_list.emplace_back(__msg_convert.Json2TaskSingle(json_data[i]));
            all_task_list.insert({map_name, task_list});
        }
        else{
            all_task_list[map_name].emplace_back(__msg_convert.Json2TaskSingle(json_data[i]));
        }
    }
    return all_task_list;
}

// 更新任务路径
void TaskDB::UpdateTaskSinglePath(string map_name, string task_name, Json::Value& json_data){
    string sql_cmd = "UPDATE TASK_SINGLE SET paths='";
    sql_cmd += json_data["task_base"]["paths"].toStyledString();
    sql_cmd += "' , edge_paths='";
    sql_cmd += json_data["task_base"]["edge_paths"].toStyledString();
    sql_cmd += "' WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "' AND task_name='";
    sql_cmd += task_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}


// 更新任务路径
void TaskDB::UpdateTaskSinglePath(string map_name, string task_name, TaskSingle& task){
    Json::Value json_data = __msg_convert.TaskSingle2Json(task);
    UpdateTaskSinglePath(map_name, task_name, json_data);
}

// 设置置顶
void TaskDB::UpdateTaskSingleIsTop(string map_name, string task_name, bool is_top){
    string sql_cmd = "UPDATE TASK_SINGLE SET is_top ='";
    sql_cmd += __msg_convert.Bool2Str(is_top);
    sql_cmd += "' WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "' AND task_name='";
    sql_cmd += task_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 更新总面积
void TaskDB::UpdateTaskArea(string map_name, string task_name, float task_area){
    string sql_cmd = "UPDATE TASK_SINGLE SET task_area ='";
    sql_cmd += to_string(task_area);
    sql_cmd += "' WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "' AND task_name='";
    sql_cmd += task_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 更新地图名称
void TaskDB::UpdateTaskSingleMapName(string old_map_name, string new_map_name){
    string sql_cmd = "UPDATE TASK_SINGLE SET map_name='";
    sql_cmd += new_map_name;
    sql_cmd += "' WHERE map_name='";
    sql_cmd += old_map_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 获取任务信息
void TaskDB::GetTaskSingle(string map_name, string task_name, TaskSingle& task_single){
    string sql_cmd = "SELECT * FROM TASK_SINGLE WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "' AND task_name='";
    sql_cmd += task_name;
    sql_cmd += "'";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    // if(json_data.isNull()){
    //     throw(task_name + string(" is not exist"));
    // }
    if(!json_data.isNull()){
        task_single = __msg_convert.Json2TaskSingle(json_data[0]);
    }
}


/*******************************************
 * 组合任务基础操作
 *******************************************/ 
// 插入组合任务
void TaskDB::InsertTaskGroup(Json::Value& json_data){
    string sql_cmd = "INSERT INTO TASK_GROUP (group_name,group_id,timertask_name,\
        timertask_id,task_list,is_top,save_time) VALUES ( '";
    sql_cmd += json_data["group_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["group_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["timertask_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["timertask_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_list"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["is_top"].asString();
    sql_cmd += "','";
    sql_cmd += __msg_convert.TimestampFormat(ros::Time::now().sec);
    sql_cmd += "')";
    TaskDBSqlite3Exec(sql_cmd);
}

// 插入组合任务
void TaskDB::InsertTaskGroup(TaskGroup& task_group){
    Json::Value json_data = __msg_convert.TaskGroup2Json(task_group);
    InsertTaskGroup(json_data);
}

//  检测组合任务是否存在
bool TaskDB::CheckTaskGroupExist(string group_name){
    string sql_cmd = "SELECT group_name,group_id FROM TASK_GROUP WHERE group_name='";
    sql_cmd += group_name;
    sql_cmd += "'";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    return !json_data.isNull();
}

// 删除组合任务
void TaskDB::DeleteTaskGroup(string group_name){
    string sql_cmd = "DELETE FROM TASK_GROUP WHERE group_name='";
    sql_cmd += group_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 删除所有组合任务
void TaskDB::DeleteAllTaskGroup(){
    string sql_cmd = "DELETE FROM TASK_GROUP";
    TaskDBSqlite3Exec(sql_cmd);
}

// 重命名组合任务
void TaskDB::RenameTaskGroup(string old_name, string new_name){
    string sql_cmd = "UPDATE TASK_GROUP SET group_name='";
    sql_cmd += new_name;
    sql_cmd += "' WHERE group_name='";
    sql_cmd += old_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 设置置顶组合任务
void TaskDB::UpdateTaskGroupIsTop(string group_name, bool is_top){
    string sql_cmd = "UPDATE TASK_GROUP SET is_top ='";
    sql_cmd += __msg_convert.Bool2Str(is_top);
    sql_cmd += "' WHERE group_name='";
    sql_cmd += group_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 更新组合任务下任务列表名称
void TaskDB::UpdateTaskGroupTaskList(TaskGroup& task_group){
    Json::Value json_data = __msg_convert.TaskGroup2Json(task_group);
    string sql_cmd = "UPDATE TASK_GROUP SET task_list='";
    sql_cmd += json_data["task_list"].toStyledString();
    sql_cmd += "' WHERE group_name='";
    sql_cmd += task_group.group_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 组合任务信息查询
bool TaskDB::GetTaskGroup(string group_name, TaskGroup& task_group){
    if(!CheckTaskGroupExist(group_name))
        return false;
    string sql_cmd = "SELECT * FROM TASK_GROUP WHERE group_name='";
    sql_cmd += group_name;
    sql_cmd += "'";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    if(json_data.isNull()){
        // throw(string(group_name + " is not exist"));
        return false;
    }
    task_group = __msg_convert.Json2TaskGroup(json_data[0]);
    return true;
}

// 组合任务列表查询
vector<TaskGroup> TaskDB::GetTaskGroupList(){
    string sql_cmd = "SELECT * FROM TASK_GROUP";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    vector<TaskGroup> taskgroups;
    for(int i=0;i<json_data.size();i++){
        taskgroups.emplace_back(__msg_convert.Json2TaskGroup(json_data[i]));
    }
    return taskgroups;
}

/*******************************************
 * 定时任务基础操作
 *******************************************/ 
// 插入定时任务
void TaskDB::InsertTimerTask(Json::Value& json_data){
    string sql_cmd = "INSERT INTO TIMER_TASK (timertask_name,timertask_id,\
        enable,start_time,forced_end_time,forced_end_time_enable,\
        timetable,taskgroup_list,estimated_time,is_top,\
        multi_maps_shared,map_name,save_time) VALUES ( '";
    sql_cmd += json_data["timertask_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["timertask_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["enable"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["start_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["forced_end_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["forced_end_time_enable"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["timetable"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["taskgroup_list"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["estimated_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["is_top"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["multi_maps_shared"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["map_name"].asString();
    sql_cmd += "','";
    sql_cmd += __msg_convert.TimestampFormat(ros::Time::now().sec);
    sql_cmd += "')";
    TaskDBSqlite3Exec(sql_cmd);
}

// 插入定时任务
void TaskDB::InsertTimerTask(TimerTask& timertask){
    Json::Value json_data = __msg_convert.TimerTask2Json(timertask);
    //cout << "[InsertTimerTask] " << json_data.toStyledString() << endl;
    InsertTimerTask(json_data);
}

//  检测定时任务是否存在
bool TaskDB::CheckTimerTaskExist(string timertask_name){
    string sql_cmd = "SELECT timertask_name,timertask_id FROM TIMER_TASK WHERE timertask_name='";
    sql_cmd += timertask_name;
    sql_cmd += "'";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    return !json_data.isNull();
}

// 删除定时任务
void TaskDB::DeleteTimerTask(string timertask_name){
    string sql_cmd = "DELETE FROM TIMER_TASK WHERE timertask_name='";
    sql_cmd += timertask_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 删除所有定时任务
void TaskDB::DeleteAllTimerTask(){
    string sql_cmd = "DELETE FROM TIMER_TASK";
    TaskDBSqlite3Exec(sql_cmd);
}

// 删除指定地图下所有定时任务
void TaskDB::DeleteTimerTaskByMapName(string map_name){
    string sql_cmd = "DELETE FROM TIMER_TASK WHERE map_name='";
    sql_cmd += map_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd); 
}

// 定时任务更新地图名称
void TaskDB::UpdateTimerTaskMapName(string old_map_name, string new_map_name){
    string sql_cmd = "UPDATE TIMER_TASK SET map_name='";
    sql_cmd += new_map_name;
    sql_cmd += "' WHERE map_name='";
    sql_cmd += old_map_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 定时任务数据整体更新
void TaskDB::UpdateTimerTask(string timer_task_name, Json::Value& new_json_data){
    string sql_cmd = "UPDATE TIMER_TASK SET timertask_name='";
    sql_cmd += new_json_data["timertask_name"].asString();
    sql_cmd += "',timertask_id='";
    sql_cmd += new_json_data["timertask_id"].asString();
    sql_cmd += "',enable='";
    sql_cmd += new_json_data["enable"].asString();
    sql_cmd += "',start_time='";
    sql_cmd += new_json_data["start_time"].asString();
    sql_cmd += "',forced_end_time='";
    sql_cmd += new_json_data["forced_end_time"].asString();
    sql_cmd += "',timetable='";
    sql_cmd += new_json_data["timetable"].toStyledString();
    sql_cmd += "',taskgroup_list='";
    sql_cmd += new_json_data["taskgroup_list"].toStyledString();
    sql_cmd += "',is_top='";
    sql_cmd += new_json_data["is_top"].asString();
    sql_cmd += "',save_time='";
    sql_cmd += __msg_convert.TimestampFormat(ros::Time::now().sec);
    sql_cmd += "' WHERE timertask_name='";
    sql_cmd += timer_task_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 定时任务数据更新
void TaskDB::UpdateTimerTask(string timer_task_name, TimerTask& new_timer_task){
    Json::Value json_data = __msg_convert.TimerTask2Json(new_timer_task);
    UpdateTimerTask(timer_task_name, json_data);
}

// 设置置顶组合任务
void TaskDB::UpdateTimerTaskIsTop(string timer_task_name, bool is_top){
    string sql_cmd = "UPDATE TIMER_TASK SET is_top ='";
    sql_cmd += __msg_convert.Bool2Str(is_top);
    sql_cmd += "' WHERE timertask_name='";
    sql_cmd += timer_task_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

// 定时任务信息查询
bool TaskDB::GetTimerTask(string timertask_name, TimerTask& timer_task){
    string sql_cmd = "SELECT * FROM TIMER_TASK WHERE timertask_name='";
    sql_cmd += timertask_name;
    sql_cmd += "'";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    if(!json_data.isNull()){
        timer_task = __msg_convert.Json2TimerTask(json_data[0]);
        return true;   
    }
    return false;
}

// 所有定时任务列表查询
vector<TimerTask> TaskDB::GetTimerTaskList(){
    vector<TimerTask> timertasks;
    string sql_cmd = "SELECT * FROM TIMER_TASK";
    Json::Value json_data = TaskDBSqlite3Exec(sql_cmd);
    for(int i=0;i<json_data.size();i++){
        timertasks.emplace_back(__msg_convert.Json2TimerTask(json_data[i]));
    }
    return timertasks;
}

// 更新定时任务下组合任务列表
void TaskDB::UpdateTimerTaskGroupList(TimerTask& timertask){
    Json::Value json_data = __msg_convert.TimerTask2Json(timertask);
    string sql_cmd = "UPDATE TIMER_TASK SET taskgroup_list='";
    sql_cmd += json_data["taskgroup_list"].toStyledString();
    sql_cmd += "' WHERE timertask_name='";
    sql_cmd += timertask.timertask_name;
    sql_cmd += "'";
    TaskDBSqlite3Exec(sql_cmd);
}

/*******************************************
 * 任务报告基础操作
 *******************************************/ 
// 单个任务记录保存
void TaskDB::InsertTaskSingleRecord(Json::Value& json_data){
    string sql_cmd = "INSERT INTO TASK_SINGLE_RECORD (task_id,task_name,map_name,\
        group_name,group_id,timertask_name,timertask_id,\
        add_time,start_time,end_time,task_status,progress,task_single,is_assigned,save_time) VALUES ( '";
    sql_cmd += json_data["task_base"]["task_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["task_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["map_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["group_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["group_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["timertask_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["timertask_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_echo"]["add_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_echo"]["start_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_echo"]["end_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_echo"]["task_status"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_echo"]["progress"].asString();
    sql_cmd += "','";
    sql_cmd += json_data.toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_base"]["is_assigned"].asString();
    sql_cmd += "','";
    sql_cmd += __msg_convert.TimestampFormat(ros::Time::now().sec);
    sql_cmd += "')";
    // cout << "[InsertTaskSingle]sql_cmd: " << sql_cmd << endl;
    TaskRecordDBSqlite3Exec(sql_cmd);
}

// 单个任务记录保存
void TaskDB::InsertTaskSingleRecord(TaskSingle& task, string timertask_name, string timertask_id){
    Json::Value json_data = __msg_convert.TaskSingle2Json(task);
    json_data["timertask_name"] = timertask_name;
    json_data["timertask_id"] = timertask_id;
    // cout << "[InsertTaskSingle]json_data: " << json_data << endl;
    InsertTaskSingleRecord(json_data);
}    

//  检测单个任务记录是否存在
bool TaskDB::CheckTaskSingleRecordExist(string task_id){
    string sql_cmd = "SELECT task_id FROM TASK_SINGLE_RECORD WHERE task_id='";
    sql_cmd += task_id;
    sql_cmd += "'";
    Json::Value json_data = TaskRecordDBSqlite3Exec(sql_cmd);
    return !json_data.isNull();
}

// 插入任务报告
void TaskDB::InsertTaskRecord(Json::Value& json_data, string save_time){
    string sql_cmd = "INSERT INTO TASK_REPORT (group_id,group_name,\
        timertask_name,timertask_id,is_assigned,start_time,end_time,working_time,\
        total_area,cleaned_area,uncleaned_area,cleaning_efficiency,\
        coverage_rate,error_code,task_pictures,status,task_group,save_time) VALUES ( '";
    sql_cmd += json_data["task_group"]["group_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_group"]["group_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_group"]["timertask_name"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_group"]["timertask_id"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["is_assigned"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["start_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["end_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["working_time"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["total_area"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["cleaned_area"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["uncleaned_area"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["cleaning_efficiency"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["coverage_rate"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["error_code"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["task_pictures"].toStyledString();
    sql_cmd += "','";
    sql_cmd += json_data["status"].asString();
    sql_cmd += "','";
    sql_cmd += json_data["task_group"].toStyledString();
    sql_cmd += "','";
    if(save_time.empty()){
        sql_cmd += __msg_convert.TimestampFormat(ros::Time::now().sec);
    } 
    else{
        sql_cmd += save_time;
    }
    sql_cmd += "')";
    // if(json_data["task_group"]["group_name"].asString() == "charging")
    // cout << "[InsertTaskRecord] sql_cmd: " << sql_cmd << endl;
    TaskRecordDBSqlite3Exec(sql_cmd);
}

// 插入任务报告
void TaskDB::InsertTaskRecord(TaskRecord& task_record){
    Json::Value json_data = __msg_convert.TaskRecord2Json(task_record);
    InsertTaskRecord(json_data);
}

// 插入任务报告
void TaskDB::InsertTaskRecordWithSaveTime(TaskRecord& task_record, string save_time){
    Json::Value json_data = __msg_convert.TaskRecord2Json(task_record);
    InsertTaskRecord(json_data, save_time);
}

//  检测任务报告是否存在
bool TaskDB::CheckTaskRecordExist(string group_id){
    string sql_cmd = "SELECT group_id FROM TASK_REPORT WHERE group_id='";
    sql_cmd += group_id;
    sql_cmd += "'";
    Json::Value json_data = TaskRecordDBSqlite3Exec(sql_cmd);
    return !json_data.isNull();
}

// 查询指定组合任务记录，需要group_id
void TaskDB::QueryTaskRecords(string group_id, vector<TaskRecord>& task_records){
    string sql_cmd = "SELECT * FROM TASK_REPORT WHERE group_id='";
    sql_cmd += group_id;
    sql_cmd += "'";
    // sql_cmd += "' ORDER BY save_time DESC";
    // cout << "[QueryTaskRecords]sql_cmd: " << sql_cmd << endl;
    Json::Value json_data = TaskRecordDBSqlite3Exec(sql_cmd);
    for(int i = 0; i < json_data.size(); ++i){
        TaskRecord task_record = __msg_convert.Json2TaskRecord(json_data[i]);
        task_records.emplace_back(task_record);
    }
}

// 查询组合任务记录（基于时间范围和页数）
// ASC 表示升序排序，可以改为 DESC 表示降序排序。
void TaskDB::FindTaskRecordFromTimeArray(string start_time, string end_time, int page, int page_size, vector<TaskRecord>& task_records){
    string sql_cmd =  "SELECT * FROM TASK_REPORT WHERE start_time BETWEEN '"
        + start_time
        + "' AND '"
        + end_time
        + "' ORDER BY end_time  DESC LIMIT '"
        + to_string(page_size)
        + "' OFFSET '"
        + to_string((page-1) * page_size)
        + "' ";
    Json::Value json_data = TaskRecordDBSqlite3Exec(sql_cmd);
    // cout << "json_data: " << json_data << endl;
    for(int i = 0; i < json_data.size(); ++i){
        TaskRecord task_record = __msg_convert.Json2TaskRecord(json_data[i]);
        // // 任务取消且进度小于0.1的不返回
        // if(task_record.status == TaskState::USER_CANCEL && task_record.coverage_rate < 0.1)
        //     continue;
        task_records.emplace_back(task_record);
    }
}


// 数据库自动清理测试 max_days天以前数据将被清理
void TaskDB::TaskRecordAutoClean(int max_days){
    ros::Time task_clean_time = ros::Time::now() - ros::Duration(max_days*24*60*60);  //__logs_time_clean天以前
    string sql_cmd_clean = "DELETE FROM TASK_SINGLE_RECORD WHERE save_time <= '" \
        + __msg_convert.TimestampFormat(task_clean_time.sec)
        + "'";
    TaskRecordDBSqlite3Exec(sql_cmd_clean);
    sql_cmd_clean = "DELETE FROM TASK_REPORT WHERE save_time <= '" \
        + __msg_convert.TimestampFormat(task_clean_time.sec)
        + "'";
    TaskRecordDBSqlite3Exec(sql_cmd_clean);
    // 清空无效磁盘数据
    sql_cmd_clean = "vacuum";
    TaskRecordDBSqlite3Exec(sql_cmd_clean);
}